package cc.messcat.service.fupin;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.stereotype.Service;

import cc.messcat.common.model.Pager;
import cc.messcat.common.service.BaseServiceImpl;
import cc.messcat.dao.area.AreaDao;
import cc.messcat.dao.fupin.RidPoorDao;
import cc.messcat.entity.area.Area;
import cc.messcat.entity.fupin.Family;
import cc.messcat.entity.fupin.FamilyHistory;
import cc.messcat.entity.fupin.PoorFamily;
import cc.messcat.entity.fupin.PoorFamilyHistory;
import cc.messcat.entity.fupin.RidPoor;
import cc.messcat.entity.system.McUser;
import cc.messcat.util.ExcelUtil;

@Service
public class RidPoorServiceImpl extends BaseServiceImpl<RidPoor> implements RidPoorService {

	@Autowired
    private RidPoorDao ridPoorDao;
	
	@Autowired
    private AreaDao areaDao;
	
	@Override
	public Pager<RidPoor> getPager(RidPoor ridPoor, String name, String idNumber, Integer pageNo, Integer pageSize,McUser user,String pid,String country, String village) {
		Map map = new HashMap<>();
	    StringBuffer hql = new StringBuffer();
	    hql.append("FROM RidPoor WHERE 1=1 ");
	    //省
        if (user.getProvince()!= null && !"".equals(user.getProvince())) {
            String province = user.getProvince().replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE province LIKE '%" + province + "%') ");
        }
        //市
        if (user.getCity()!= null && !"".equals(user.getCity())) {
            String city = user.getCity() .replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE city LIKE '%" + city + "%')");
        }
        //县
        if (user.getCty()!= null && !"".equals(user.getCty())) {
            String county = user.getCty().replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE county LIKE '%" + county + "%')");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(country != null && !"".equals(country)){
        		   Area area = areaDao.getAreaByCode(country);
                   hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE country LIKE '%" + area.getArea() + "%')");
                   
        	   }else{
        		   Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
               	   String cry = area.getArea(); 
                   hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE country LIKE '%" + cry + "%')");
        	   }            	
        }else{
        	 if(country != null && !"".equals(country)){
      		      Area area = areaDao.getAreaByCode(country);
                 hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE country LIKE '%" + area.getArea() + "%')");
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(village!= null && !"".equals(village)){
        		Area area = areaDao.getAreaByCode(village);
                hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE village LIKE '%" + area.getArea() + "%')");
        	}else{
        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
            	String name2 = area.getArea(); 
            	hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE village LIKE '%" + name2 + "%')");
        	}       	
        }else{
        	if(village!= null && !"".equals(village)){
        		Area area = areaDao.getAreaByCode(village);
                hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE village LIKE '%" + area.getArea() + "%')");
        	}
        }
	    //业务年度
        if (StringUtils.isNotBlank(ridPoor.getAnnual())) {
            hql.append(" AND annual = '" + ridPoor.getAnnual() + "'");
        }
        //姓名
        if (name!= null && !"".equals(name)) {
        	name = name.replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT fid FROM Family WHERE name LIKE '%" + name + "%')");
        }
        //证件号码
        if (idNumber!= null && !"".equals(idNumber)) {
        	idNumber = idNumber.replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT fid FROM Family WHERE idNumber LIKE '%" + idNumber + "%')");
        }
        //变更类型
        if (ridPoor.getChanges()!= null && !"".equals(ridPoor.getChanges())) {
            String changes = ridPoor.getChanges().replaceAll(" ", "");
            hql.append(" AND changes = '" + changes + "'");
        }
       
        if (pid!=null && !"".equals(pid)) {
        	 //变更后的贫困户
        	if(pid=="1"){
                hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily");
            }
            //变更前的贫困户
            if(pid=="2"){
            	hql.append(" AND poorFamilyHistory.originId in (SELECT id FROM PoorFamily");
                }
		}
        hql.append(" AND originPoorFamily.id !='null' AND poorFamilyHistory.id !='null'");
		return this.ridPoorDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
        }

	@SuppressWarnings("unchecked")
	@Override
	public HSSFWorkbook exportVisitRecd(RidPoor ridPoor,String name,String idNumber,McUser mcUser,String pid, String country, String village) {
		String[] cellName = new String[]
				{"序号","姓名","证件号码","变更年度","经办时间","变更类型",
						"变更原因","贫困户属性","主要致贫原因","脱贫状态","识别标准",
						"联系电话","开户银行名称","银行卡号"};
		String[] field = new String[] 
				{"name","idNumber","years","addTime","changes",
				"reason","attribute","reasons","poverty","standard",
				"contact","bank","cardNumber"};
		
		Integer sheetCount = 0;
		Pager pager = null;
		Map<Integer,List> excelResult = new HashMap<>();

		
		do {
			sheetCount++;
			pager  = getPager(ridPoor,name,idNumber,sheetCount, 5000,mcUser,pid,country,village);
			excelResult.put(sheetCount, pager.getResultList());
			
		} while(sheetCount < (pager.getRowCount()+4999) / 5000);
		
		//变更类型  ''为全部、0为返贫    1为已脱贫 11已脱贫(不再享受政策)   2为家庭成员信息变更、3为家庭成员增加、4为家庭成员减少、5为户基本信息变更、6为户主变更、7为家庭成员转移、8为新识别贫困户、9为预脱贫、10为注销
		for(Object e :pager.getResultList()) {
			if (((RidPoor)e).getChanges().equals("1")) {
				((RidPoor)e).setChanges("已脱贫(享受政策)");
			}
			if (((RidPoor)e).getChanges().equals("11")) {
				((RidPoor)e).setChanges("已脱贫(不再享受政策)");
			}
			if (((RidPoor)e).getChanges().equals("9")) {
				((RidPoor)e).setChanges("未脱贫");
			}
			if (((RidPoor)e).getChanges().equals("0")) {
				((RidPoor)e).setChanges("返贫");
			}
			if (((RidPoor)e).getChanges().equals("2")) {
				((RidPoor)e).setChanges("家庭成员信息变更");
			}
			if (((RidPoor)e).getChanges().equals("3")) {
				((RidPoor)e).setChanges("家庭成员增加");
			}
			if (((RidPoor)e).getChanges().equals("4")) {
				((RidPoor)e).setChanges("家庭成员减少");
			}
			if (((RidPoor)e).getChanges().equals("5")) {
				((RidPoor)e).setChanges("户基本信息变更");
			}
			if (((RidPoor)e).getChanges().equals("6")) {
				((RidPoor)e).setChanges("户主变更");
			}
			if (((RidPoor)e).getChanges().equals("7")) {
				((RidPoor)e).setChanges("家庭成员转移");
			}
			if (((RidPoor)e).getChanges().equals("8")) {
				((RidPoor)e).setChanges("新识别贫困户");
			}
			if (((RidPoor)e).getChanges().equals("10")) {
				((RidPoor)e).setChanges("注销");
			}
		}
		
		
		List fList = new ArrayList<>();
		for(Object e :pager.getResultList()) {
			List<Family> family = ((RidPoor)e).getOriginPoorFamily().getFamily();
			Map map = new LinkedHashMap<>();
			map.put("years",((RidPoor)e).getAnnual());
			map.put("changes",((RidPoor)e).getChanges());
			map.put("reason",((RidPoor)e).getReason());
			map.put("addTime",((RidPoor)e).getPoorFamilyHistory().getAddTime());
			map.put("attribute",((RidPoor)e).getPoorFamilyHistory().getAttribute());
			map.put("reasons",((RidPoor)e).getPoorFamilyHistory().getReason());
			map.put("poverty",((RidPoor)e).getPoorFamilyHistory().getPoverty());
			map.put("standard",((RidPoor)e).getPoorFamilyHistory().getStandard());
			map.put("contact",((RidPoor)e).getPoorFamilyHistory().getContact());
			map.put("bank",((RidPoor)e).getPoorFamilyHistory().getBank());
			map.put("cardNumber",((RidPoor)e).getPoorFamilyHistory().getCardNumber());
			
			if (family != null && family.size() > 0) {
				for (Family family2 : family) {
					if ("户主".equals(family2.getRelation())) {
						map.put("name", family2.getName());
						map.put("idNumber", family2.getIdNumber());
						map = new LinkedHashMap<>(map);
					} 
				}
			}
			fList.add(map);
		}
		
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		/*VisitRecd  a  =(VisitRecd)pager.getResultList();
		a.getPoorFamily().getFamily().get(0).getName();*/
//		familyDao.queryList("select *from ");
		//名字为贫困户户主姓名_走访记录.xls
		for(int i = 1; i <= excelResult.size(); i++) {
			HSSFSheet sheet = workbook.createSheet("贫困户变更" + i);
			sheet.setDefaultColumnWidth(17);
			
			HSSFCellStyle headStyle =  ExcelUtil.getTitleStyle(workbook);
			HSSFCellStyle cellStyle =  ExcelUtil.getCellStyle(workbook);
			HSSFCellStyle columnTitleStyle =  ExcelUtil.getColumnTitleStyle(workbook);

			ExcelUtil.addTitle(sheet,cellName,"贫困户变更",headStyle,columnTitleStyle);
			ExcelUtil.addContextByList(sheet, fList, field, cellStyle, true);
		}
		
		return workbook;
	}

	@Override
	public Pager<RidPoor> getPagertwo(RidPoor ridPoor, String name, String idNumber, Integer pageNo, Integer pageSize,McUser user, String pid, String country, String village) {
		Map map = new HashMap<>();
	    StringBuffer hql = new StringBuffer();
	    hql.append("FROM RidPoor WHERE 1=1 ");
	    //省
        if (user.getProvince()!= null && !"".equals(user.getProvince())) {
            String province = user.getProvince().replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE province LIKE '%" + province + "%') ");
        }
        //市
        if (user.getCity()!= null && !"".equals(user.getCity())) {
            String city = user.getCity() .replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE city LIKE '%" + city + "%')");
        }
        //县
        if (user.getCty()!= null && !"".equals(user.getCty())) {
            String county = user.getCty().replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE county LIKE '%" + county + "%')");
        }
        //乡
        if(user.getUserLevel().getLevel() == 4){
        	   if(country != null && !"".equals(country)){
        		   Area area = areaDao.getAreaByCode(country);
                   hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE country LIKE '%" + area.getArea() + "%')");
                   
        	   }else{
        		   Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
               	   String cry = area.getArea(); 
                   hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE country LIKE '%" + cry + "%')");
        	   }            	
        }else{
        	 if(country != null && !"".equals(country)){
      		      Area area = areaDao.getAreaByCode(country);
                 hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE country LIKE '%" + area.getArea() + "%')");
      	   }
        }
        
        //村        
        if(user.getUserLevel().getLevel() == 5){
        	if(village!= null && !"".equals(village)){
        		Area area = areaDao.getAreaByCode(village);
                hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE village LIKE '%" + area.getArea() + "%')");
        	}else{
        		Area area = areaDao.getAreaByCode(user.getArea().getAreaCode());
            	String name2 = area.getArea(); 
            	hql.append("  AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE village LIKE '%" + name2 + "%')");
        	}       	
        }else{
        	if(village!= null && !"".equals(village)){
        		Area area = areaDao.getAreaByCode(village);
                hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily WHERE village LIKE '%" + area.getArea() + "%')");
        	}
        }
	    //业务年度
        if (StringUtils.isNotBlank(ridPoor.getAnnual())) {
            hql.append(" AND annual = '" + ridPoor.getAnnual() + "'");
        }
        //姓名
        if (name!= null && !"".equals(name)) {
        	name = name.replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT fid FROM Family WHERE name LIKE '%" + name + "%')");
        }
        //证件号码
        if (idNumber!= null && !"".equals(idNumber)) {
        	idNumber = idNumber.replaceAll(" ", "");
            hql.append(" AND originPoorFamily.id in (SELECT fid FROM Family WHERE idNumber LIKE '%" + idNumber + "%')");
        }
        //变更类型
        if (ridPoor.getChanges()!= null && !"".equals(ridPoor.getChanges())) {
            String changes = ridPoor.getChanges().replaceAll(" ", "");
            hql.append(" AND changes = '" + changes + "'");
        }
       
        if (pid!=null && !"".equals(pid)) {
        	 //变更后的贫困户
        	if(pid=="1"){
                hql.append(" AND originPoorFamily.id in (SELECT id FROM PoorFamily");
            }
            //变更前的贫困户
            if(pid=="2"){
            	hql.append(" AND poorFamilyHistory.originId in (SELECT id FROM PoorFamily");
                }
		}
        hql.append(" AND originPoorFamily.id !='null' AND poorFamilyHistory.id !='null'");
//        hql.append(" AND poorFamilyHistory.originId in (SELECT fid FROM Family WHERE relation = '户主') ");
		Pager pager = ridPoorDao.getPagerByHql(pageNo, pageSize, hql.toString(), map);
		
		//户主查找
		for (RidPoor e : (List<RidPoor>)pager.getResultList()) {
			PoorFamily originPoorFamily = e.getOriginPoorFamily();
			PoorFamilyHistory poorFamilyHistory = e.getPoorFamilyHistory();
			if(originPoorFamily == null || poorFamilyHistory == null) continue;
			List<Family> family = originPoorFamily.getFamily();
			if (family != null && family.size() > 0) {
				for (Family family2 : family) {
					if ("户主".equals(family2.getRelation())) {
						List<FamilyHistory> familyHistories = new ArrayList<>();
						FamilyHistory familyHistory = new FamilyHistory();
						BeanUtils.copyProperties(family2, familyHistory);
						familyHistory.setId(null);
						familyHistories.add(familyHistory);
						poorFamilyHistory.setFamilyHistories(familyHistories);
					}
				}
			}
		}
		return pager;
	}

	@Override
	public List<RidPoor> RidPoor(Long id) {
		 Map map = new HashMap<>();
	     String hql = "from RidPoor where id= :id ";
	     map.put("id", id);
	  return this.ridPoorDao.queryList(hql, map);
	}
	}


